const db = require("../../lib/conn_oracle");
const encryption = require("../../util/encryption")
const duplicate = require('../../util/duplicate')

//班组信息获取
exports.pgs = (req,res)=>{
    const pgsSel = `select persongroup,pgname from c##wxw.persongroup
                    where cust_type <> '作废'
                    order by persongroup
                    `
    let datas
    async function fun(){
        let pgs = await db.search2(pgsSel)
        if(pgs.length){
            datas = {pgs};        
            res.json({
                status:1,
                msg:"查询成功",
                datas,
            });   
        }else{
            res.json({
                status:0,
                msg:"查询失败",
                datas
            });
        }
    }
    fun()
}

//用户信息获取
exports.users = (req,res)=>{
    // console.log(req.body)
    // let {persongroup,realname} = req.query 
    let {form} = req.body
    let {persongroup,realname} = form
    // console.log(persongroup,realname);
    
    let pgWhere = persongroup.length > 0 ? `and persongroup = ${persongroup}` : ''
    let rnWhere = realname.length > 0 ? `and realname like '%${realname}%'` : ''
    let usersSel = `select id,personid,username,realname,password,remark,persongroup,status
                    from c##wxw.vue_xfjcusers
                    where 1 = 1
                    ${pgWhere}
                    ${rnWhere}
                    order by persongroup,personid                    
                    `
    let datas 
    async function fun(){
        let usersInfo = await db.search2(usersSel)
        datas = {usersInfo}
        // console.log(datas)        
        res.json({
            status:1,
            msg:"查询成功",
            datas
        })
    }
    fun()
}

//处理单个用户
exports.handleUser = async (req,res)=>{
    // console.log(req.body);
    let {type,psIsChange} = req.body  
    let {ID,PERSONID,USERNAME,REALNAME,PASSWORD,REMARK,PERSONGROUP,STATUS} = req.body.form
    psIsChange = psIsChange === true ? true : false //密码是否修改
    // console.log(typeof psIsChange);
    // console.log(`old密码：${PASSWORD}`)        
    if (type !== "delete" && psIsChange) PASSWORD = await encryption.hashedPassword(PASSWORD) //密码加密d
    // console.log(`new密码：${PASSWORD}`)    
    let datas
    let handleSql
    let handleData
    let submitTypeStr
    let status
    /**
     * 数据库处理函数
     * @param {String} type 处理类型
     * @param {String} sql  sql语句
     * @param {Array} data  参数（以数组形式传递，批量处理使用嵌套数组）
     */
    const cudFun = async (type,sql,data)=>{
        if(type === "add"){
            let [PERSONID,USERNAME] = data
            let existsData = [PERSONID,USERNAME]
            let existsUserSql = `select count(id) as cu
                                from c##wxw.vue_xfjcusers
                                where (personid = :PERSONID or username = :USERNAME)                          
                                `  
            let existsUser = await db.search(existsUserSql,existsData)
            // console.log(existsUser)
            if(existsUser[0].CU > 0){
                msg = "用户已存在，新增失败!"
                return{
                    status:0,
                    msg
                }
            }else{
                let newUsers = await db.writeTab(sql,data)
                datas = {newUsers}         
                msg = datas.newUsers.errorNum ? `${submitTypeStr}失败，错误代码：ORA-` + datas.newUsers : `${submitTypeStr}成功`
                status = datas.newUsers.errorNum ? 0 : 1
                return{
                    status,
                    msg,
                    datas
                }
            }
        }else{
            let newUsers = await db.writeTab(sql,data)
            datas = {newUsers}         
            msg = datas.newUsers.errorNum ? `${submitTypeStr}失败，错误代码：ORA-` + datas.newUsers : `${submitTypeStr}成功`
            status = datas.newUsers.errorNum ? 0 : 1
            return{
                status,
                msg,
                datas
            }
        }        
    }

    if(type === 'update'){
        submitTypeStr = "更新"
        if(psIsChange){
            handleData = [USERNAME,REALNAME,PASSWORD,REMARK,PERSONGROUP,STATUS,ID,PERSONID]
            handleSql = `
                        update c##wxw.vue_xfjcusers
                        set 
                        username = :USERNAME,
                        realname = :REALNAME,
                        password = :PASSWORD,
                        remark = :REMARK,
                        persongroup = :PERSONGROUP,
                        status = :STATUS
                        where id = :ID
                        and personid = :PERSONID                    
                        `
        }else{
            handleData = [USERNAME,REALNAME,REMARK,PERSONGROUP,STATUS,ID,PERSONID]
            handleSql = `
                        update c##wxw.vue_xfjcusers
                        set 
                        username = :USERNAME,
                        realname = :REALNAME,
                        remark = :REMARK,
                        persongroup = :PERSONGROUP,
                        status = :STATUS
                        where id = :ID
                        and personid = :PERSONID                    
                        `
        }       
        // console.log(handleSql)        
        let newUsers = await cudFun(type,handleSql,handleData)
        res.json(newUsers)
    }
    if(type === 'add' ){
        submitTypeStr = "新增"
        handleData = [PERSONID,USERNAME,REALNAME,PASSWORD,REMARK,PERSONGROUP,STATUS]
        handleSql = `insert into c##wxw.vue_xfjcusers(id,personid,username,realname,password,remark,persongroup,status)
                     values('',:PERSONID,:USERNAME,:REALNAME,:PASSWORD,:REMARK,:PERSONGROUP,:STATUS)
                     `        
        let newUsers = await cudFun(type,handleSql,handleData)        
        res.json(newUsers)
    }
    if(type === 'delete'){
        submitTypeStr = "删除"
        handleData = [ID]
        handleDataPersonId = [PERSONID]
        handleSql = `delete c##wxw.vue_xfjcusers where id = :ID`
        let newUsers = await cudFun(type,handleSql,handleData)
        if(newUsers.msg.includes("失败")){
            newUsers.msg = "用户删除失败"
            res.json(newUsers)
        }else{
            handleSql = `delete c##wxw.vue_xfjcuser_role_middle where personid = :PERSONID`
            let newUsers = await cudFun(type,handleSql,handleDataPersonId)
            if(newUsers.msg.includes("失败")){
                newUsers.msg = "角色分配对应的用户删除失败"
                res.json(newUsers)
            }else{
                newUsers.msg = "用户删除成功"
                res.json(newUsers)
            }
        }
    }      
}

//批量处理用户
exports.batchHandleUsers = async (req,res)=>{
    let {type,form:{batchDataArr,batchPidArr}} = req.body
    // console.log(type)
    // console.log(batchDataArr)
    // console.log(batchPidArr)
    let newUpdateArr = []
    let newAddArr = []
    let batchEditSql  
    let msg
    let datas
    let status
    let options = {
        autoCommit: true,
    }
    let hasPidAndUn = [] //用户批量提交的在数据库已存在的用户
    if(type === 'batchDel' && batchDataArr){
        batchEditSql = `delete from c##wxw.vue_xfjcusers where id = :ID`
    }else if(type === 'batchUpdate' && batchDataArr){
        batchEditSql =  `update c##wxw.vue_xfjcusers
                        set  
                        username = :USERNAME,
                        realname = :REALNAME,
                        password = :PASSWORD,
                        remark = :REMARK,
                        persongroup = :PERSONGROUP,
                        status = :STATUS
                        where id = :ID
                        and personid = :PERSONID
                        ` 
           
        for(let i = 0;i < batchDataArr.length;i++){
            //创建新用户对象，update时与SQL参数顺序一致
           let newUpdate = {
                USERNAME:"",
                REALNAME:"",
                PASSWORD:"",
                REMARK:"",
                PERSONGROUP:"",
                STATUS:1,
                ID:"",
                PERSONID:""
            } 
            newUpdate.USERNAME = batchDataArr[i].USERNAME
            newUpdate.REALNAME  = batchDataArr[i].REALNAME
            newUpdate.PASSWORD  = batchDataArr[i].PASSWORD.length ? await encryption.hashedPassword(batchDataArr[i].PASSWORD) : ""
            newUpdate.REMARK  = batchDataArr[i].REMARK
            newUpdate.PERSONGROUP  = batchDataArr[i].PERSONGROUP
            newUpdate.STATUS = batchDataArr[i].STATUS
            newUpdate.ID  = batchDataArr[i].ID
            newUpdate.PERSONID = batchDataArr[i].PERSONID
            newUpdate = Object.values(newUpdate) //转换为数组ORACLE需要接收嵌套数组
            newUpdateArr.push(newUpdate)
        }
        // console.log(newUpdateArr);
        batchDataArr = newUpdateArr
    }else if(type === 'batchAdd' && batchDataArr){
        batchEditSql = `insert into c##wxw.vue_xfjcusers(id,personid,username,realname,password,remark,persongroup,status)
                        values(:ID,:PERSONID,:USERNAME,:REALNAME,:PASSWORD,:REMARK,:PERSONGROUP,:STATUS)
                        `
        let usersSel = `select personid,username from c##wxw.vue_xfjcusers`
        for(let i = 0;i < batchDataArr.length;i++){
            //创建新用户对象，update时与SQL参数顺序一致
           let newAdd = {
                ID:"",
                PERSONID:"",
                USERNAME:"",
                REALNAME:"",
                PASSWORD:"",
                REMARK:"",
                PERSONGROUP:"",
                STATUS:1,                
            } 
            newAdd.ID  = batchDataArr[i].ID
            newAdd.PERSONID = batchDataArr[i].PERSONID
            newAdd.USERNAME = batchDataArr[i].USERNAME
            newAdd.REALNAME  = batchDataArr[i].REALNAME
            newAdd.PASSWORD  = batchDataArr[i].PASSWORD.length ? await encryption.hashedPassword(batchDataArr[i].PASSWORD) : ""
            newAdd.REMARK  = batchDataArr[i].REMARK
            newAdd.PERSONGROUP  = batchDataArr[i].PERSONGROUP
            newAdd.STATUS = batchDataArr[i].STATUS            
            newAdd = Object.values(newAdd) //转换为数组ORACLE需要接收嵌套数组
            newAddArr.push(newAdd)
        }
        batchDataArr = newAddArr
        //提取用户名、personid
        let addPidsAndUns = []
        let uPidsAndUns = []
        for(let i = 0;i < batchDataArr.length;i++){            
            addPidsAndUns.push(Number(batchDataArr[i][0]),batchDataArr[i][1])
        }
        // console.log(addPidsAndUns);
        //查询已存在用户
        async function uFun(){
            let users = await db.search2(usersSel) 
            for(let i = 0;i < users.length;i++){
                uPidsAndUns.push(users[i].PERSONID,users[i].USERNAME)
            } 
            // console.log(uPidsAndUns);       
        }
        await uFun()
        //用户批量提交的与数据库已存在的用户进行对比
        hasPidAndUn = duplicate.duplicate_query(addPidsAndUns,uPidsAndUns)
        // console.log(hasPidAndUn);        
    }

    async function fun(){
        if(hasPidAndUn.length){
            status = 0
            msg = `以下用户已存在，请修改：${hasPidAndUn}`
            res.json({
                status,
                msg,
                datas
            })
        }else{
            let batchEditUsers = await db.writeManyTab(batchEditSql,batchDataArr,options)
            datas = {batchEditUsers}
            // console.log(datas.batchEditUsers.code);
            if(type === 'batchDel'){
                if(datas.batchEditUsers.errorNum || datas.batchEditUsers.code ){
                    msg = "删除失败，错误代码：ORA-" + datas.batchEditUsers
                    status = 0
                }else{
                    let batchDelUrmSel = `delete from c##wxw.vue_xfjcuser_role_middle 
                                          where personid = :PERSONID
                                        `
                    let batchDelUrm = await db.writeManyTab(batchDelUrmSel,batchPidArr,options)
                    let batchUrmDatas = {batchDelUrm}
                    msg = batchUrmDatas.batchDelUrm.errorNum ?  "删除失败，错误代码：ORA-" + batchUrmDatas.batchDelUrm : "删除用户及对应角色成功"
                    status = batchUrmDatas.batchDelUrm.errorNum ? 0 : 1
                }
            }else if(type === 'batchAdd' || type === 'batchUpdate'){
                if(datas.batchEditUsers.errorNum || datas.batchEditUsers.code ){
                    if(type === 'batchAdd') msg = "新增失败，错误代码：ORA-" + datas.batchEditUsers
                    if(type === 'batchUpdate') msg = "更新失败，错误代码：ORA-" + datas.batchEditUsers
                    status = 0
                }else{
                    if(type === 'batchAdd') msg = "新增成功" 
                    if(type === 'batchUpdate') msg = "更新成功"
                    status = 1
                }            
            }
            res.json({
                status,
                msg,
                datas
            })        
        }        
        
    }
    fun()  
    // res.json({
    //     status:1,
    // })      
}